clear
set more off
capture log using log/figure1.log, replace

*1. Exports 
*i. get currency data with indicator
*2017m2 to 2019m3
*************************************************************
use ../../rawdata/update_feb21/currency_extraEU_2017_2020, clear
	drop *I
	drop if missing(currency)
	drop if missing(valueX)
	rename valueX xvalue
	rename country cty_code 	 	
 gen eurozone = inlist(cty_code, "AT", "CY", "EE", "FI", "FR", "DE", "GR", "IE", "IT") 
	replace eurozone =1 if inlist(cty_code, "LV", "LT", "LU", "MT", "NL", "PT", "SK", "SI", "ES")
	replace eurozone = 1 if inlist(cty_code, "AD", "MC", "SM", "XK", "ME") 
	drop if eurozone==1
       	merge m:1 cty_code using ../rawdata/cty_name, keep(match master) nogen	
    	
    	gen cat="EUR" if currency=="EUR"
	replace cat="USD" if currency=="USD"
	replace cat="other" if missing(cat)
   	
*drop countries that don't appear in trade data
	drop if inlist(cty_code, "HM", "NR", "QX")
	drop if cty_code=="EU"
	
	
*split count shares and value shares by diff/nondif
	gen hs6=substr(cncode,1,6)
	merge m:1 hs6 using ../data/Rauch_hs6_concordance, keep(match master) 
	tab _merge
		
	gen dif=con=="n"
	gen count=1

*by country - all
preserve	
	
	gen xvalue_euro=xvalue if cat=="EUR"
	gen xvalue_usd=xvalue if cat=="USD"
	gen xvalue_other=xvalue if cat=="other"
	
	collapse (sum) xvalue*, by(cty_code cty_name)	
	gen shxvalue_euro=xvalue_euro/xvalue
	gen shxvalue_usd=xvalue_usd/xvalue
	
	label var shxvalue_euro "share of euro currency in exports by destination"
	label var shxvalue_usd "share of usd currency in exports by destination"
	keep cty_name cty_code shxvalue* xvalue 
	sort cty_name	

export excel ../results/sumstats/Figure1, sheet("Exports_all") firstrow(variables) replace keepcellfmt
restore

*by country - diff
keep if dif==1
gen xvalue_euro=xvalue if cat=="EUR"
	gen xvalue_usd=xvalue if cat=="USD"
	gen xvalue_other=xvalue if cat=="other"
	
	collapse (sum) xvalue*, by(cty_code cty_name)	
	gen shxvalue_euro=xvalue_euro/xvalue
	gen shxvalue_usd=xvalue_usd/xvalue
	
	label var shxvalue_euro "share of euro currency in exports by destination"
	label var shxvalue_usd "share of usd currency in exports by destination"
	keep cty_name cty_code shxvalue* xvalue 
	sort cty_name	

export excel ../results/sumstats/Figure1, sheet("Exports_diff", replace) firstrow(variables) keepcellfmt


*2. Imports 
*******************
use ../../rawdata/update_feb21/currency_extraEU_2017_2020, clear
	drop *X
	drop if missing(currency)
	drop if missing(valueI)
	rename valueI mvalue
	rename country cty_code 	
 
 gen eurozone = inlist(cty_code, "AT", "CY", "EE", "FI", "FR", "DE", "GR", "IE", "IT") 
 	replace eurozone =1 if inlist(cty_code, "LV", "LT", "LU", "MT", "NL", "PT", "SK", "SI", "ES")
	replace eurozone = 1 if inlist(cty_code, "AD", "MC", "SM", "XK", "ME")
	drop if eurozone==1
     	merge m:1 cty_code using ../rawdata/cty_name, keep(match master) nogen
    	gen cat="EUR" if currency=="EUR"
	replace cat="USD" if currency=="USD"
	replace cat="other" if missing(cat)
	
*drop countries that don't appear in trade data
	drop if inlist(cty_code, "HM", "NR", "QX")	

*these don't appear in import trade	
	drop if inlist(cty_code, "BV", "CX", "EH", "ER", "FK", "FM", "GS", "KM" )
	drop if inlist(cty_code, "MH", "NU", "PM", "PN", "QV", "RS", "SO", "WF")
	drop if cty_code=="EU"

	
*split count shares and value shares by diff/nondif
	gen hs6=substr(cncode,1,6)
	merge m:1 hs6 using ../data/Rauch_hs6_concordance, keep(match master) 
	tab _merge
	gen dif=con=="n"
	gen count=1

*by country - all
preserve	
	gen mvalue_euro=mvalue if cat=="EUR"
	gen mvalue_usd=mvalue if cat=="USD"
	gen mvalue_other=mvalue if cat=="other"
	
	collapse (sum) mvalue*, by(cty_code cty_name)	
	gen shmvalue_euro=mvalue_euro/mvalue
	gen shmvalue_usd=mvalue_usd/mvalue
	
	label var shmvalue_euro "share of euro currency in exports by destination"
	label var shmvalue_usd "share of usd currency in exports by destination"
		
	keep cty_name cty_code shmvalue* mvalue 
	sort cty_name	

export excel ../results/sumstats/Figure1, sheet("Imports_all", replace) firstrow(variables) keepcellfmt
restore

*by country - diff
keep if dif==1
gen mvalue_euro=mvalue if cat=="EUR"
	gen mvalue_usd=mvalue if cat=="USD"
	gen mvalue_other=mvalue if cat=="other"
	
	collapse (sum) mvalue*, by(cty_code cty_name)	
	gen shmvalue_euro=mvalue_euro/mvalue
	gen shmvalue_usd=mvalue_usd/mvalue
	
	label var shmvalue_euro "share of euro currency in exports by destination"
	label var shmvalue_usd "share of usd currency in exports by destination"
		
	keep cty_name cty_code shmvalue* mvalue 
	sort cty_name	
export excel ../results/sumstats/Figure1, sheet("Imports_diff", replace) firstrow(variables) keepcellfmt	

log close



